package com.jms.fu.model.dao;

import java.io.UnsupportedEncodingException;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.jms.fu.common.Common;
import com.jms.fu.exception.DAOException;
import com.jms.fu.model.bean.Project;
import com.jms.fu.model.bean.User;

public class ProjectDAO {
	// Init variable
		Connection conn = null;
		PreparedStatement pStm = null;
		Statement stm = null;
		ResultSet rs = null;
	public void createNewProject(String name, String managerId, String datecreate) throws DAOException {
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "INSERT INTO tb_Project (name,managerId,datecreate,status) VALUES (?,?,?,0)";

			pStm = conn.prepareStatement(sql);
			pStm.setString(1, name);
			pStm.setString(2, managerId);
			pStm.setString(3, datecreate);
			pStm.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
	}
	public boolean checkName(String name) throws DAOException {
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "SELECT name FROM tb_Project WHERE name =?";

			pStm = conn.prepareStatement(sql);
			pStm.setString(1, name);
			rs = pStm.executeQuery();

			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
		return false;
	}
	public List<Project> getProjectByManager(String username) throws DAOException {
		Project temp;
		ArrayList<Project> list = new ArrayList<>();
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "SELECT * FROM tb_Project WHERE managerId=?";
			pStm = conn.prepareStatement(sql);
			pStm.setString(1,username);
			rs=pStm.executeQuery();
			while(rs.next()){
				temp= new Project();
				temp.setProjectId(rs.getInt(1));
				temp.setName(rs.getString(2));
				temp.setDatecreate(rs.getDate(4));
				temp.setStatus(rs.getInt(5));
				
				list.add(temp);
			}
			return list;
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
	}
	public void closeProject(String projectId) throws DAOException {
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "UPDATE tb_Project SET status='1' WHERE projectId=?";
			pStm = conn.prepareStatement(sql);
			pStm.setString(1, projectId);
			pStm.executeUpdate();
			String sql1 = "UPDATE tb_User SET projectId='0' WHERE projectId=?";
			pStm = conn.prepareStatement(sql1);
			pStm.setString(1, projectId);
			pStm.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());

		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
	
	}
	public void assignMember(String username, String projectId) throws DAOException {
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "UPDATE tb_User SET projectId=? WHERE userId=?";
			pStm = conn.prepareStatement(sql);
			pStm.setString(1, projectId);
			pStm.setString (2,username);
			pStm.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());

		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
		
	}
	public void kickOut(String username) throws DAOException {
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "UPDATE tb_User SET projectId=0 WHERE userId=?";
			pStm = conn.prepareStatement(sql);
			pStm.setString (1,username);
			pStm.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());

		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}		
	}

}
